use AutoAncillariesLimited
go

---
create procedure findProductsByCategoryID
	@categoryID varchar(200),
	@pageSize int,
	@currentPage int,
	@itemCount int output
as
begin

	declare @upperBand int, @lowerBand int
	
	set @itemCount = (select count(*) from Products where CategoryId=@categoryID)

	set @lowerBand  = (@currentPage - 1) * @pageSize
	set @upperBand  = (@currentPage * @pageSize) + 1

	;with tempPaged as
	(select *,ROW_NUMBER() over (order by p.ProductID asc) as RowNumber
		from Products p where CategoryId=@categoryID)
	select * 
	from tempPaged 
	where RowNumber > @lowerBand AND RowNumber < @upperBand
return
end
go
----
--declare @out int
--exec  getAllProductByCategoryID 'C001',1,2,@out
create procedure findProductsByCategoryID1
	@categoryID varchar(200)
as
begin
	select * from Products where CategoryID=@categoryID
end
go
---
